package com.shsh.safemanage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.shsh.safemanage.model.Problem;
import com.shsh.safemanage.model.ProblemTransfer;
import com.shsh.safemanage.util.Constants;
import com.shsh.safemanage.util.ValueUtil;

public class ProblemDao {
	public boolean addProblem(Problem problem) {
		String sql = "insert into problem(PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, DEPARTMENT1_ID, " +
				"EQUIPMENT_ID, WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS) values(?, ?, ?, ?, ?, ?, ?, ?) " +
				"select @@identity as PROBLEM_ID";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			pstm.setString(1, problem.getProblemTitle());
			pstm.setString(2, problem.getProblemContent());
			pstm.setString(3, problem.getProblemZhenggai());
			pstm.setLong(4, problem.getDepartment1ID());
			pstm.setLong(5, problem.getEquipmentID());
			pstm.setLong(6, problem.getWriteUserID());
			pstm.setTimestamp(7, new Timestamp(problem.getWriteTime().getTimeInMillis()));
			pstm.setInt(8, problem.getProcessStatus());
			
			pstm.executeUpdate();
			ResultSet rs = pstm.getGeneratedKeys();
			if (rs.next()) {
				problem.setId(rs.getLong("PROBLEM_ID"));
			}
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
	
	public boolean addProblemTransfers(List<ProblemTransfer> transferList) {
		String sql = "insert into problem_transfer([USER_ID], PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, IS_MAIN_TRANSFERED_USER) " +
				"values(?, ?, ?, ?, ?)";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			for (ProblemTransfer transfer : transferList) {
				pstm.setLong(1, transfer.getUserID());
				pstm.setLong(2, transfer.getProblemID());
				pstm.setLong(3, transfer.getTransferedUserID());
				pstm.setBoolean(4, Boolean.FALSE);
				pstm.setBoolean(5, transfer.isMainTransferUser());
				
				pstm.executeUpdate();
			}
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
	
	public List<Problem> searchProblems(Long department1ID, Long equipmentID, String problemTitle, 
			String problemContent, String dateStart, String dateEnd) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, " +
				"WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, CLOSE_USER_ID, CLOSE_TIME, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME " +
				"from problem left outer join employee on problem.WRITE_USER_ID=employee.ID " +
				"left outer join department1 on problem.DEPARTMENT1_ID=department1.ID " +
				"left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " +
				"where ";
		
		boolean hasPre = false;
		if (department1ID != null) {
			sql += "problem.DEPARTMENT1_ID=" + department1ID + " ";
			hasPre = true;
		}
		
		if (equipmentID != null) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "EQUIPMENT_ID=" + equipmentID + " ";
			hasPre = true;
		}
		
		if (problemTitle != null && problemTitle.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_TITLE like '%" + problemTitle.trim() + "%' ";
			hasPre = true;
		}
		
		if (problemContent != null && problemContent.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "PROBLEM_CONTENT like '%" + problemContent + "%' ";
			hasPre = true;
		}
		
		if (dateStart != null && dateStart.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME>='" + dateStart + " 00:00:00' ";
			hasPre = true;
		}
		
		if (dateEnd != null && dateEnd.trim().length() > 0) {
			if (hasPre) {
				sql += "and ";
			}
			sql += "WRITE_TIME<='" + dateEnd + " 23:59:59' ";
			hasPre = true;
		}
		
		sql += " order by WRITE_TIME desc";
		
		List<Problem> problemList = new ArrayList<Problem>();
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setCloseUserID(rs.getLong("CLOSE_USER_ID"));
				problem.setCloseTime(ValueUtil.getCalendar(rs.getObject("CLOSE_TIME")));
				
				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return problemList;
	}
	
	public Problem getProblemByID(Long id) {
		String sql = "select problem.ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, problem.DEPARTMENT1_ID, EQUIPMENT_ID, " +
				"WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, employee.NAME, department1.DEPARTMENT1_NAME, equipment.EQUIP_NAME " +
				"from problem left outer join employee on problem.WRITE_USER_ID=employee.ID " +
				"left outer join department1 on problem.DEPARTMENT1_ID=department1.ID " +
				"left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " +
				"where problem.ID=?";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, id);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				Problem problem = new Problem();
				problem.setId(rs.getLong("ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				
				return problem;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public List<ProblemTransfer> getProblemTransferByProblemID(Long problemID) {
		String sql = "select problem_transfer.ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, FEED_BACK, " +
				"PROCESS_TIME, employee.NAME, employee.DEPARTMENT1_ID " +
				"from problem_transfer left outer join employee on problem_transfer.TRANSFERED_USER_ID=employee.ID " +
				"where PROBLEM_ID=? order by PROCESS_TIME asc";
		
		List<ProblemTransfer> transferList = new ArrayList<ProblemTransfer>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, problemID);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				ProblemTransfer transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("PROBLEM_ID"));
				transfer.setTransferedUserID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setFeedback(rs.getString("FEED_BACK"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setTransferedUserName(rs.getString("NAME"));
				transfer.setTransferedUserDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				
				transferList.add(transfer);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return transferList;
	}
	
	public List<Problem> getProblemsTobeProcessed(Long userID) {
		String sql = "select problem_transfer.ID, problem_transfer.PROBLEM_ID, PROBLEM_TITLE, PROBLEM_CONTENT, PROBLEM_ZHENGGAI_CUOSHI, " +
				"problem.DEPARTMENT1_ID, EQUIPMENT_ID, department1.DEPARTMENT1_NAME, EQUIP_NAME, " +
				"WRITE_USER_ID, WRITE_TIME, PROCESS_STATUS, employee.NAME from problem_transfer " +
				"left outer join problem on problem_transfer.PROBLEM_ID=problem.ID " +
				"left outer join employee on problem.WRITE_USER_ID=employee.ID " +
				"left outer join department1 on problem.DEPARTMENT1_ID=department1.ID " +
				"left outer join equipment on problem.EQUIPMENT_ID=equipment.ID " +
				"where problem_transfer.TRANSFERED_USER_ID=? and IS_PROCESSED=0";
		List<Problem> problemList = new ArrayList<Problem>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, userID);
			
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Problem problem = new Problem();
				problem.setTransferID(rs.getLong("ID"));
				problem.setId(rs.getLong("PROBLEM_ID"));
				problem.setProblemTitle(rs.getString("PROBLEM_TITLE"));
				problem.setProblemContent(rs.getString("PROBLEM_CONTENT"));
				problem.setProblemZhenggai(rs.getString("PROBLEM_ZHENGGAI_CUOSHI"));
				problem.setDepartment1ID(rs.getLong("DEPARTMENT1_ID"));
				problem.setDepartment1Name(rs.getString("DEPARTMENT1_NAME"));
				problem.setEquipmentID(rs.getLong("EQUIPMENT_ID"));
				problem.setEquipmentName(rs.getString("EQUIP_NAME"));
				problem.setWriteUserID(rs.getLong("WRITE_USER_ID"));
				problem.setWriteTime(ValueUtil.getCalendar(rs.getObject("WRITE_TIME")));
				problem.setProcessStatus(rs.getInt("PROCESS_STATUS"));
				problem.setWriteUserName(rs.getString("NAME"));
				
				problemList.add(problem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return problemList;
	}
	
	public boolean isMainProblemProcessor(Long transferedUserID, Long problemID, Long transferID) {
		String sql = "select ID from problem_transfer where TRANSFERED_USER_ID=? and PROBLEM_ID=? and ID=? " +
				"and IS_PROCESSED=0 and IS_MAIN_TRANSFERED_USER=1";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			pstm.setLong(1, transferedUserID);
			pstm.setLong(2, problemID);
			pstm.setLong(3, transferID);
			
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
	
	public ProblemTransfer getProblemTransferByID(Long id) {
		String sql = "select ID, USER_ID, PROBLEM_ID, TRANSFERED_USER_ID, IS_PROCESSED, FEED_BACK, " +
				"PROCESS_TIME, IS_MAIN_TRANSFERED_USER from problem_transfer where ID=?";
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, id);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				ProblemTransfer transfer = new ProblemTransfer();
				transfer.setId(rs.getLong("ID"));
				transfer.setUserID(rs.getLong("USER_ID"));
				transfer.setProblemID(rs.getLong("PROBLEM_ID"));
				transfer.setTransferedUserID(rs.getLong("TRANSFERED_USER_ID"));
				transfer.setProcessed(rs.getBoolean("IS_PROCESSED"));
				transfer.setFeedback(rs.getString("FEED_BACK"));
				transfer.setProcessTime(ValueUtil.getCalendar(rs.getObject("PROCESS_TIME")));
				transfer.setMainTransferUser(rs.getBoolean("IS_MAIN_TRANSFERED_USER"));
				
				return transfer;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return null;
	}
	
	public List<Long> getTransferRoleUserByTransferedUserID(Long problemID, int roleType) {
		String sql = "select distinct employee.ID from problem_transfer " +
				"left outer join employee on problem_transfer.USER_ID=employee.ID " +
				"left outer join job_post on employee.JOB_POST1_ID=job_post.ID or employee.JOB_POST2_ID=job_post.ID or employee.JOB_POST2_ID=job_post.ID " +
				"left outer join role on job_post.ROLE_ID=role.ID " +
				"where problem_transfer.PROBLEM_ID=? and (role.ROLE_TYPE & ?) > 0";
		List<Long> userIDList = new ArrayList<Long>();
		
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, problemID);
			pstm.setInt(2, roleType);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				userIDList.add(rs.getLong("ID"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return userIDList;
	}
	
	public boolean processProblem(String feedback, Long transferID, Long problemID, Long userID, int status) {
		String sql1 = "update problem_transfer set FEED_BACK=?, PROCESS_TIME=?, IS_PROCESSED=1 where ID=?";
		String sql2 = "update problem set PROCESS_STATUS=?, CLOSE_USER_ID=?, CLOSE_TIME=? where ID=?";
		String sql3 = "update problem_transfer set IS_PROCESSED=1 where PROBLEM_ID=?";
		
		Connection conn = null;
		PreparedStatement pstm1 = null;
		PreparedStatement pstm2 = null;
		PreparedStatement pstm3 = null;
		try {
			conn = ConnectionManager.getSafeManageConn();
			conn.setAutoCommit(false);
			
			pstm1 = conn.prepareStatement(sql1);
			pstm2 = conn.prepareStatement(sql2);
			pstm3 = conn.prepareStatement(sql3);
			
			pstm1.setString(1, feedback);
			pstm1.setTimestamp(2, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			pstm1.setLong(3, transferID);
			
			pstm2.setInt(1, status);
			if (status == Constants.PROBLEM_STATUS_CLOSE) {
				pstm2.setLong(2, userID);
				pstm2.setTimestamp(3, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			} else {
				pstm2.setNull(2, Types.INTEGER);
				pstm2.setNull(3, Types.TIMESTAMP);
			}
			pstm2.setLong(4, problemID);
			
			pstm3.setLong(1, problemID);
			
			pstm1.executeUpdate();
			pstm2.executeUpdate();
			if (status == Constants.PROBLEM_STATUS_CLOSE) {
				pstm3.executeUpdate();
			}
			
			conn.commit();
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		
		return false;
	}
}
